ALTER SYSEM
ALTER SYSTEM — Change a Server Configuration Parameter
Synopsis
ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT }
ALTER SYSTEM RESET configuration_parameter
ALTER SYSTEM RESET ALL
Description
ALTER SYSTEM is used to change server configuration parameters across the entire database cluster. It is more convenient than the traditional method of manually editing the postgresql.conf file. ALTER SYSTEM writes the given parameter setting to the postgresql.auto.conf file, which is read in together with postgresql.conf. Setting a parameter to DEFAULT or using the RESET variant removes the configuration entry from the postgresql.auto.conf file.
Use RESET ALL to remove all such configuration entries.
Values set with ALTER SYSTEM take effect after the next server configuration reload, or for parameters that can only be changed at server startup, after the next server restart. The server configuration can be reloaded by calling the SQL function pg_reload_conf(), running pg_ctl reload, or sending a SIGHUP signal to the main server process.
Only superusers can use ALTER SYSTEM. Additionally, since this command acts directly on the file system and cannot be rolled back, it is not allowed inside a transaction block or a function.
Parameters
configuration_parameter
The name of a settable configuration parameter.
value
The new value of the parameter. Values can be specified as string constants, identifiers, numbers, or comma-separated lists of these, depending on the particular parameter. Writing DEFAULT can be used to remove the parameter and its value from postgresql.auto.conf.
Notes
This command cannot be used to set data_directory or parameters that are not allowed in postgresql.conf (such as preset options).
Examples
# Set wal_level:
ALTER SYSTEM SET wal_level = replica;
# Undo the above, restoring whatever setting is in effect in postgresql.conf:
ALTER SYSTEM RESET wal_level;